using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;
namespace DAO
{
    public class CKhachHangDao
    {
        public void CKHThem(CKhachHangDto aKhachHang)
        {
            OleDbConnection oledbcnKetnoi = DataProvider.ConnectionData();;
            string sChuoitruyvan = "INSERT INTO KhachHang(HoTen,NgaySinh,CMND,DiaChi,Email,DienThoai,TenDangNhap,MatKhau) VALUES(?,?,?,?,?,?,?,?)";
            OleDbCommand oledbcmTruyvan = new OleDbCommand(sChuoitruyvan, oledbcnKetnoi);

            oledbcmTruyvan.Parameters.Add("hoten", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("ngaysinh", OleDbType.Date, 20);
            oledbcmTruyvan.Parameters.Add("CMND", OleDbType.LongVarChar, 10);
            oledbcmTruyvan.Parameters.Add("DiaChi", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("Email", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("DienThoai", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("TenDangNhap", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("MatKhau", OleDbType.LongVarChar, 50);

            oledbcmTruyvan.Parameters["hoten"].Value = aKhachHang.m_sHoTen;
            oledbcmTruyvan.Parameters["ngaysinh"].Value = aKhachHang.m_dtNgaySinh;
            oledbcmTruyvan.Parameters["cmnd"].Value = aKhachHang.m_sCMND;
            oledbcmTruyvan.Parameters["diachi"].Value = aKhachHang.m_sDiaChi;
            oledbcmTruyvan.Parameters["email"].Value = aKhachHang.m_sEmail;
            oledbcmTruyvan.Parameters["dienthoai"].Value = aKhachHang.m_sDienThoai;
            oledbcmTruyvan.Parameters["tendangnhap"].Value = aKhachHang.m_sTenDangNhap;
            oledbcmTruyvan.Parameters["matkhau"].Value = aKhachHang.m_sMatKhau;
            

            oledbcmTruyvan.ExecuteNonQuery();

            oledbcnKetnoi.Close();
            oledbcnKetnoi.Dispose();
            oledbcmTruyvan.Dispose();


        }

        public void CKHXoa(int aid)
        {
            OleDbConnection oledbcnKetnoi = DataProvider.ConnectionData();
            OleDbCommand oledbcmTruyvan = null;


            string sChuoitruyvan = "DELETE * FROM KhachHang WHERE MaKhachHang=?";
            oledbcmTruyvan = new OleDbCommand(sChuoitruyvan, oledbcnKetnoi);

            oledbcmTruyvan.Parameters.Add("makhachhang", OleDbType.Integer);


            oledbcmTruyvan.Parameters["makhachhang"].Value = aid;


            oledbcmTruyvan.ExecuteNonQuery();








            oledbcnKetnoi.Close();
            oledbcnKetnoi.Dispose();
            oledbcmTruyvan.Dispose();
        }
        public DataTable CKHLayDS()
        {
            DataTable dtDS = new DataTable();
            OleDbConnection oledbcnKetnoi = DataProvider.ConnectionData();
            string sChuoitruyvan = "SELECT * FROM KhachHang";
            OleDbCommand oledbcmTruyvan = new OleDbCommand(sChuoitruyvan, oledbcnKetnoi);
            OleDbDataAdapter oledbadpt = new OleDbDataAdapter(oledbcmTruyvan);

            //oledbadpt.FillSchema(dtDS, SchemaType.Source);
            oledbadpt.Fill(dtDS);


            oledbcnKetnoi.Close();
            oledbcnKetnoi.Dispose();
            oledbcmTruyvan.Dispose();
            oledbadpt.Dispose();

            return dtDS;
        }


        public void CKHCapNhat(CKhachHangDto aKhachHang, int aid)
        {
            OleDbConnection oledbcnKetnoi = DataProvider.ConnectionData();;
            string sChuoitruyvan = "UPDATE KhachHang SET HoTen=? , NgaySinh=? , CMND=? , DiaChi=? , Email=? , DienThoai=?, MatKhau=? WHERE MaKhachHang=?";
            OleDbCommand oledbcmTruyvan = new OleDbCommand(sChuoitruyvan, oledbcnKetnoi);

            oledbcmTruyvan.Parameters.Add("hoten", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("ngaysinh", OleDbType.Date, 20);
            oledbcmTruyvan.Parameters.Add("CMND", OleDbType.LongVarChar, 10);
            oledbcmTruyvan.Parameters.Add("DiaChi", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("Email", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("DienThoai", OleDbType.LongVarChar, 50);
//            oledbcmTruyvan.Parameters.Add("TenDangNhap", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("MatKhau", OleDbType.LongVarChar, 50);
            oledbcmTruyvan.Parameters.Add("makhachhang", OleDbType.Integer);

            oledbcmTruyvan.Parameters["hoten"].Value = aKhachHang.m_sHoTen;
            oledbcmTruyvan.Parameters["ngaysinh"].Value = aKhachHang.m_dtNgaySinh;
            oledbcmTruyvan.Parameters["cmnd"].Value = aKhachHang.m_sCMND;
            oledbcmTruyvan.Parameters["diachi"].Value = aKhachHang.m_sDiaChi;
            oledbcmTruyvan.Parameters["email"].Value = aKhachHang.m_sEmail;
            oledbcmTruyvan.Parameters["dienthoai"].Value = aKhachHang.m_sDienThoai;
           // oledbcmTruyvan.Parameters["tendangnhap"].Value = aKhachHang.m_sTenDangNhap;
            oledbcmTruyvan.Parameters["matkhau"].Value = aKhachHang.m_sMatKhau;
                   
            oledbcmTruyvan.Parameters["makhachhang"].Value = aid;
            
            oledbcmTruyvan.ExecuteNonQuery();

            oledbcnKetnoi.Close();
            oledbcnKetnoi.Dispose();
            oledbcmTruyvan.Dispose();
        }


        public int TimMaKH(string TenDangNhap, string MatKhau)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From KhachHang Where TenDangNhap = ? and MatKhau = ? ";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenDangNhap", OleDbType.WChar);
            cmd.Parameters.Add("@MatKhau", OleDbType.WChar);
            cmd.Parameters["@TenDangNhap"].Value = TenDangNhap;
            cmd.Parameters["@MatKhau"].Value = MatKhau;

            OleDbDataReader dr = cmd.ExecuteReader();
            int MaKH = -1;
            while (dr.Read())
            {
                MaKH = (int)dr["MaKhachHang"];
            }

            //B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return MaKH;

        }

        //public CKhachHangDto CKHTim(CKhachHangDto aKhachHang, int aCachtim)
        //{
        //    CKhachHangDto CKHDtoKH = null;
        //    OleDbConnection oledbcnKetnoi = DataProvider.ConnectionData();;
        //    OleDbDataReader oledbread = null;
        //    if (aCachtim == 0)
        //    {
        //        string sChuoitruyvan = "SELECT * FROM KhachHang WHERE MaKhachHang=?";
        //        OleDbCommand oledbcmTruyvan = new OleDbCommand(sChuoitruyvan, oledbcnKetnoi);

        //        oledbcmTruyvan.Parameters.Add("makhachhang", OleDbType.Integer);


        //        oledbcmTruyvan.Parameters["makhachhang"].Value = aKhachHang.m_iMaKhachHang;


        //        oledbread = oledbcmTruyvan.ExecuteReader();

        //        if (oledbread.Read())
        //        {
        //            CKHDtoKH = new CKhachHangDto();
        //            CKHDtoKH.m_iMaKhachHang = (int)oledbread[0];
        //            CKHDtoKH.m_sHoTen = oledbread[1].ToString();
        //            CKHDtoKH.m_dtNgaySinh = Convert.ToDateTime(oledbread[2]);
        //        }

        //    }


        //    if (aCachtim == 1)
        //    {



        //        string sChuoitruyvan = "SELECT * FROM KhachHang WHERE HoTen=? AND NgaySinh=?";
        //        OleDbCommand oledbcmTruyvan = new OleDbCommand(sChuoitruyvan, oledbcnKetnoi);

        //        oledbcmTruyvan.Parameters.Add("hoten", OleDbType.LongVarChar, 50);
        //        oledbcmTruyvan.Parameters.Add("ngaysinh", OleDbType.Date, 20);

        //        oledbcmTruyvan.Parameters["hoten"].Value = aKhachHang.m_sHoTen;
        //        oledbcmTruyvan.Parameters["ngaysinh"].Value = aKhachHang.m_dtNgaySinh;

        //        oledbread = oledbcmTruyvan.ExecuteReader();
        //        if (oledbread.Read())
        //        {
        //            CKHDtoKH = new CKhachHangDto();

        //            CKHDtoKH.m_iMaKhachHang = (int)oledbread[0];
        //            CKHDtoKH.m_sHoTen = oledbread[1].ToString();
        //            CKHDtoKH.m_dtNgaySinh = Convert.ToDateTime(oledbread[2]);
        //        }

        //    }


        //    oledbcnKetnoi.Close();
        //    oledbcnKetnoi.Dispose();

        //    oledbread.Close();
        //    oledbread.Dispose();


        //    return CKHDtoKH;
        //}
    }
}
